﻿using Microsoft.Office.Interop.Excel;
using MiniExcelLibs;
using Sunny.UI;
using Sunny.UI.Win32;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace PanhuaWin.仓库.采购入库
{
    public partial class Form卷号转换 : UIPage
    {
        public Form卷号转换()
        {
            InitializeComponent();
        }

        private void Form卷号转换_Load(object sender, EventArgs e)
        {
            this.uiDatePicker开始日期.Value = DateTime.Today.AddDays(-10);
            this.uiDatePicker结束日期.Value = DateTime.Today;
            pub.ListWorkShop(pub.sys_user, this.uiComboBoxWorkShop);
        }

        private void uiButton刷新_Click(object sender, EventArgs e)
        {
            ListData();
           

        }

        private void ListData()
        {
            string sql;
            this.uiDataGridView合同原料卷.DataSource = null;
            this.uiDataGridView合同原料卷.Refresh();
            sql = "SELECT  row_number() over(order by VolumeCode) 序号,  CreateDate 入库日期, DocCode 入库单号,  RowNumber 入库行号, WarehouseCode 仓库编号, StorageLocation 库位编号, " +
                "    T_CONTRACT_CODE 合同号, T_ORDER 合同行号,   VolumeCode 原料卷号, " +
                "  VolumeCodeNew 新原料卷号, InventoryCode 存货编号, Size 规格型号,  Material 材质, Address 产地, Weight 卷重量,T_GOOD_NAME 存货名称 ,T_GOOD_UNIT 计量单位  FROM      View_StockIn where 1=1 ";

            sql += " and CreateDate>='" + this.uiDatePicker开始日期.Value + "' and CreateDate< '" + this.uiDatePicker结束日期.Value.AddDays(1) + "'";
            sql += " and WorkShopCode ='" + this.uiComboBoxWorkShop.SelectedValue.ToString() + "'";
            if (!this.uiCheckBox包含已有新卷号.Checked)
            {
                 sql += " and (VolumeCodeNew ='' or VolumeCodeNew is   null) " ;
            }
            System.Data.DataTable dt = pub.GetDataTable(pub.ConnectionString, sql);
            if (dt != null)
            {
                System.Data.DataTable tb1 = pub.GetDataTable(pub.ConnectionString, sql);
                this.uiDataGridView合同原料卷.AutoGenerateColumns = false;
                this.uiDataGridView合同原料卷.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
                this.uiDataGridView合同原料卷.DataSource = tb1;
                this.uiDataGridView合同原料卷.Refresh();

            }
            else
            {

            }
        }

        private void uiDataGridView合同原料卷_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            string 原料卷号;
            string 新原料卷号;
            string 入库单号;
            if (e.RowIndex >=0)
            {
                if (this.uiDataGridView合同原料卷.Columns[e.ColumnIndex].Name =="C确认")
                {
                    原料卷号 = this.uiDataGridView合同原料卷.Rows[e.RowIndex].Cells["C原料卷号"].Value.ToString();
                    新原料卷号 = this.uiDataGridView合同原料卷.Rows[e.RowIndex].Cells["C新原料卷号"].Value.ToString();
                    入库单号 = this.uiDataGridView合同原料卷.Rows[e.RowIndex].Cells["C入库单号"].Value.ToString();
                    if (新原料卷号 == "")
                    {
                        MessageBox.Show("新原料卷号为空,请重试!", "提示", MessageBoxButtons.OK);
                        return;
                    }
                    if (pub.IsExistVolume(原料卷号))
                    {
                        if (pub.IsExistVolumeNew(新原料卷号))
                        {
                            MessageBox.Show("新原料卷号已经存在,请重试!", "提示", MessageBoxButtons.OK);
                            return;
                        }
                        else
                        {
                            string sql;
                            sql = "update Bus_StockInVolume set VolumeCodeNew ='" + 新原料卷号 + "' where VolumeCode='" + 原料卷号 + "' and doccode='" + 入库单号 + "'";
                            pub.ExecuteSql(pub.ConnectionString, sql, "给原料卷补充新的原料卷号");

                        }
                    }
                    else
                    {
                        MessageBox.Show("原料卷号不存在,请重试!", "提示", MessageBoxButtons.OK);
                        return;

                    }
                }
               

            }
        }

        private void uiButton导出模板_Click(object sender, EventArgs e)
        {
            string path;
            this.saveFileDialog1.Filter = "Excel 文件(*.xlsx)|*.xlsx ";
            string sql;
            sql = "SELECT  row_number() over(order by VolumeCode) 序号,  CreateDate 入库日期, DocCode 入库单号,  RowNumber 入库行号, WarehouseCode 仓库编号, StorageLocation 库位编号, " +
                 "    T_CONTRACT_CODE 合同号, T_ORDER 合同行号,   VolumeCode 原料卷号, " +
                 "  VolumeCodeNew 新原料卷号, InventoryCode 存货编号, Size 规格型号,  Material 材质, Address 产地, Weight 卷重量,T_GOOD_NAME 存货名称 ,T_GOOD_UNIT 计量单位 FROM      View_StockIn where 1=2";


            System.Data.DataTable dt = pub.GetDataTable(pub.ConnectionString, sql);

            if (this.saveFileDialog1.ShowDialog(this) == DialogResult.OK)
            {
                path = this.saveFileDialog1.FileName;
                if (!File.Exists(path))
                {
                    MiniExcel.SaveAs(path, dt, true, "sheet1", ExcelType.XLSX);
                }
                else
                {
                    MessageBox.Show("文件已经存在,请重试!", "提示", MessageBoxButtons.OK);
                    return;
                }

            }
        }

        private void uiButton导入_Click(object sender, EventArgs e)
        {
            try
            {
                string path;
                string 原料卷号;

                this.openFileDialog1.Filter = "Excel 文件(*.xlsx)|*.xlsx ";
                if (this.openFileDialog1.ShowDialog(this) == DialogResult.OK)
                {
                    path = this.openFileDialog1.FileName;
                    System.Data.DataTable table = (System.Data.DataTable)MiniExcel.QueryAsDataTable(path, useHeaderRow: true);
                    if (table.Rows.Count > 0)
                    {

                        
                        this.uiDataGridView合同原料卷.AutoGenerateColumns = false;
                        this.uiDataGridView合同原料卷.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
                        this.uiDataGridView合同原料卷.DataSource = table;
                        this.uiDataGridView合同原料卷.Refresh();

                    }
                    else
                    {
                        this.uiDataGridView合同原料卷.AutoGenerateColumns = false;
                        this.uiDataGridView合同原料卷.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
                        this.uiDataGridView合同原料卷.DataSource = null;
                        this.uiDataGridView合同原料卷.Refresh();
                    }


                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("导入失败了!" + ex.ToString(), "提示", MessageBoxButtons.OK);
                pub.Delete辅料入库临时数据();
                return;
            }
        }

        private void uiButton导出数据_Click(object sender, EventArgs e)
        {
            string path;
            this.saveFileDialog1.Filter = "Excel 文件(*.xlsx)|*.xlsx ";
            string sql;
            sql = "SELECT  row_number() over(order by VolumeCode) 序号,  CreateDate 入库日期, DocCode 入库单号,  RowNumber 入库行号, WarehouseCode 仓库编号, StorageLocation 库位编号, " +
               "    T_CONTRACT_CODE 合同号, T_ORDER 合同行号,   VolumeCode 原料卷号, " +
               "  VolumeCodeNew 新原料卷号, InventoryCode 存货编号, Size 规格型号,  Material 材质, Address 产地, Weight 卷重量,T_GOOD_NAME 存货名称 ,T_GOOD_UNIT 计量单位 FROM      View_StockIn where 1=1 ";

            sql += " and CreateDate>='" + this.uiDatePicker开始日期.Value + "' and CreateDate< '" + this.uiDatePicker结束日期.Value.AddDays(1) + "'";
            sql += " and WorkShopCode ='" + this.uiComboBoxWorkShop.SelectedValue.ToString() + "'";
            System.Data.DataTable dt = pub.GetDataTable(pub.ConnectionString, sql);

            if (this.saveFileDialog1.ShowDialog(this) == DialogResult.OK)
            {
                path = this.saveFileDialog1.FileName;
                if (!File.Exists(path))
                {
                    MiniExcel.SaveAs(path, dt, true, "sheet1", ExcelType.XLSX);
                }
                else
                {
                    MessageBox.Show("文件已经存在,请重试!", "提示", MessageBoxButtons.OK);
                    return;
                }

            }
        }

        private void uiButton确认_Click(object sender, EventArgs e)
        {
            string 原料卷号;
            string 新原料卷号;
            string 入库单号;
            int r = 0;
            for (int i= 0; i < uiDataGridView合同原料卷.Rows.Count; i++)
            {
                原料卷号 = this.uiDataGridView合同原料卷.Rows[i].Cells["C原料卷号"].Value.ToString();
                新原料卷号 = this.uiDataGridView合同原料卷.Rows[i].Cells["C新原料卷号"].Value.ToString();
                入库单号 = this.uiDataGridView合同原料卷.Rows[i].Cells["C入库单号"].Value.ToString();
                if (新原料卷号 == "")
                {
                    MessageBox.Show("新原料卷号为空,请重试!", "提示", MessageBoxButtons.OK);
                    //return;
                }
                else
                {
                    if (pub.IsExistVolume(原料卷号))
                    {
                        if (pub.IsExistVolumeNew(新原料卷号))
                        {
                            MessageBox.Show("第" + (i + 1).ToString() + "行的新原料卷号已经存在,请重试!", "提示", MessageBoxButtons.OK);
                            //return;
                        }
                        else
                        {
                            string sql;
                            sql = " update Bus_StockInVolume set VolumeCodeNew ='" + 新原料卷号 + "' where VolumeCode='" + 原料卷号 + "' and doccode='" + 入库单号 + "' ";
                            sql += " and (VolumeCodeNew ='' or VolumeCodeNew is null )";
                            if (pub.ExecuteSql(pub.ConnectionString, sql, "给原料卷补充新的原料卷号") == 1)
                            {
                                r++;
                            }
                            else
                            {
                                MessageBox.Show("第" + (i + 1).ToString() + "行的新原料卷号更新失败,请检查!", "提示", MessageBoxButtons.OK);

                                //return;
                            }

                        }
                    }
                    else
                    {
                        MessageBox.Show("第" + (i + 1).ToString() + "行的原料卷号不存在,请重试!", "提示", MessageBoxButtons.OK);
                        //return;

                    }
                }


            }
            if (r > 0)
            {
                if (r < uiDataGridView合同原料卷.Rows.Count)
                {
                    MessageBox.Show("有部分未成功,请重试!", "提示", MessageBoxButtons.OK);
                }
                else
                {
                    MessageBox.Show("所有都成功!", "提示", MessageBoxButtons.OK);
                }
            }
            else
            {
                MessageBox.Show("所有都未成功,请重试!", "提示", MessageBoxButtons.OK);
            }
        }
    }
}
